package com.smm.services.impl;

import com.smm.services.IDepotSheetService;
import com.smm.util.DBHelper;
import com.smm.util.PropertiesReader;
import org.json.JSONObject;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class DepotSheetService implements IDepotSheetService{

	
	/**
	*捆包实时查询接口
	*orgSysNo 交易平台代码
	*warehouseCode 仓储代码
	*goodId 实物唯一编码
	**/
	public String getRealTimeWarehouseReceiptInfo(String orgSysNo,String warehouseCode,String goodId){

		JSONObject result = new JSONObject(); 

 		if(orgSysNo==null || (orgSysNo.trim()).equals("")){

 			result.put("RTN_CODE", "0");
 			result.put("RTN_MSG", "请输入交易平台代码");

 			return result.toString();
 		}

 		if(goodId==null || (goodId.trim()).equals("")){

 			result.put("RTN_CODE", "0");
 			result.put("RTN_MSG", "请输入仓单编号");

 			return result.toString();
 		}

 		JSONObject data=this.getReceiptInfo(orgSysNo,warehouseCode,goodId);

 		if(data!=null){

 			result.put("RTN_CODE", "1");
 			result.put("RTN_MSG", "");
 			result.put("RTN_DATA", data);

 		}else{

 			result.put("RTN_CODE", "0");
 			result.put("RTN_MSG", "无数据!");
 		}
 		
		return result.toString();
	}

	/**
	*仓单统计数据清算接口
	*orgSysNo 交易平台编号
	*warehouseCode 仓库编号
	*custCode 货主代码
	*custName 货主名称
	*prdCataName 交易品种名称
	*prdCataCode 交易品种代码
	**/
	public String getRealTimeWarehouseReceiptStatisticsList(String orgSysNo,String warehouseCode,String custCode,String custName,
		String prdCataName,String prdCataCode){


		JSONObject result = new JSONObject(); 

 		if(orgSysNo==null || (orgSysNo.trim()).equals("")){

 			result.put("RTN_CODE", "0");
 			result.put("RTN_MSG", "请输入交易平台代码");

 			return result.toString();
 		}

 		if(custCode==null || (custCode.trim()).equals("")){

 			result.put("RTN_CODE", "0");
 			result.put("RTN_MSG", "请输入货主代码");

 			return result.toString();
 		}

 		if(prdCataCode==null || (prdCataCode.trim()).equals("")){

 			result.put("RTN_CODE", "0");
 			result.put("RTN_MSG", "请输入货物代码");

 			return result.toString();
 		}

 		//获取统计数据
 		JSONObject data=this.getReceiptStatisticsList(orgSysNo,warehouseCode,custCode,custName,prdCataName,prdCataCode);

 		if(data!=null){

 			result.put("RTN_CODE", "1");
 			result.put("RTN_MSG", "");
 			result.put("RTN_DATA", data);

 		}else{

 			result.put("RTN_CODE", "0");
 			result.put("RTN_MSG", "无数据!");
 		}
 		
		return result.toString();
	}


	//获取指定货主、指定货物的总重量、总数量
	private HashMap getAllWeightAndNum(String orgSysNo,String custCode,String prdCataCode){

		HashMap<String,String> dataMap=new HashMap<>();

		String sql = "select SUM(WeightB) as all_weight,SUM(SheetNum) as all_warenum  from receipt where 1=1 ";//SQL语句

		sql+=" and marketno='"+orgSysNo+"'";
		sql+=" and OwnerCode='"+custCode+"'";
		sql+=" and warecode='"+prdCataCode+"'";


		DBHelper db1 = new DBHelper(sql);//创建DBHelper对象  
  
        try {  

            ResultSet ret = db1.pst.executeQuery();//执行语句，得到结果集  

            if (ret.next()) {  

            	dataMap.put("all_weight",ret.getString("all_weight"));
            	dataMap.put("all_warenum",ret.getString("all_warenum"));
 				
            }else{

            	return null;
            }

            ret.close();  
            db1.close();//关闭连接  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } 

        return dataMap;
	}


	//获取清算数据
	private JSONObject getReceiptStatisticsList(String orgSysNo,String warehouseCode,String custCode,String custName,
		String prdCataName,String prdCataCode){

		//将公示平台传递的D代码替换为交易中心简称
		PropertiesReader reader=new PropertiesReader("/mark.properties");
       	String mark_no=reader.getValue("mark.no");

       	if(orgSysNo.equals(mark_no)){

       		orgSysNo=reader.getValue("mark.name");
       	}

		JSONObject data=new JSONObject();

		String sql = "select * from receipt where 1=1 ";//SQL语句  

		sql+=" and marketno='"+orgSysNo+"'";
		sql+=" and OwnerCode='"+custCode+"'";
		sql+=" and warecode='"+prdCataCode+"'";

		if(warehouseCode!=null && !(warehouseCode.trim()).equals("")) sql+=" and depotno='"+warehouseCode+"'";
		if(custName!=null && !(custName.trim()).equals("")) sql+=" and OwnerName like '%"+custName+"%'";
		if(prdCataName!=null && !(prdCataName.trim()).equals("")) sql+=" and warename like '%"+prdCataName+"%'";

		System.out.println(sql);

		//获取指定货主、直指定商品的总数量、总重量
		Map resultMap=getAllWeightAndNum(orgSysNo,custCode,prdCataCode);

		if(resultMap==null) return null;

		//查询其他信息
        DBHelper db1 = new DBHelper(sql);//创建DBHelper对象  
  
        try {  

            ResultSet ret = db1.pst.executeQuery();//执行语句，得到结果集  

            if (ret.next()) {  

            	// data.put("orgSysNo",ret.getString("marketno"));
            	data.put("orgSysNo",mark_no);
            	data.put("warehouseCode",ret.getString("depotno"));
 				data.put("warehouseName",ret.getString("depotname"));

				//交易品种名称
				data.put("prdCataName",ret.getString("GoodsProductName"));
				//交易品种代码
				data.put("prdCataCode",ret.getString("warecode"));
				//海关商品编号
				data.put("ctm_prdCode",ret.getString("warecstmno"));

				//品名描述
				data.put("typeName",ret.getString("GoodsProductName"));
 				
 				data.put("weight_b",resultMap.get("all_weight"));
 				data.put("weight_unit",ret.getString("WeightUnit"));
 				
 				data.put("sheetNum",resultMap.get("all_warenum"));
 				data.put("unit",ret.getString("SheetNumUnit"));
 				
 				
            }else{

            	return null;
            }

            ret.close();  
            db1.close();//关闭连接  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } 

        return  data;


	}


	//获取仓单信息
	private JSONObject getReceiptInfo(String orgSysNo,String warehouseCode,String goodId){

		JSONObject data=new JSONObject();

		//将公示平台传递的D代码替换为市场简称
		PropertiesReader reader=new PropertiesReader("/mark.properties");
       	String mark_no=reader.getValue("mark.no");

       	if(orgSysNo.equals(mark_no)){

       		orgSysNo=reader.getValue("mark.name");
       	}


		String sql = "select * from receipt where receiptno='"+goodId+"' and marketno='"+orgSysNo+"'";//SQL语句

		if(warehouseCode!=null && !(warehouseCode.trim()).equals("")){

			sql+=" and depotno='"+warehouseCode+"'";
		}  


		System.out.println(sql);

        DBHelper db1 = new DBHelper(sql);//创建DBHelper对象  
  
        try {  

            ResultSet ret = db1.pst.executeQuery();//执行语句，得到结果集  

            if (ret.next()) {  
    
 				// data.put("orgSysNo",ret.getString("marketno"));

 				//按公示平台要求，返回交易平台D代码
 				// PropertiesReader reader=new PropertiesReader("/mark.properties");
     //        	String mark_no=reader.getValue("mark.no");
				//交易市场编码（交易中心在公示平台的编号）
 				data.put("orgSysNo",mark_no);

				//仓库编码
 				data.put("warehouseCode",ret.getString("depotno"));
				//仓库名称
 				data.put("warehouseName",ret.getString("depotname"));
				//仓单编号
 				data.put("goodId",ret.getString("receiptno"));

				//货主代码
 				data.put("custCode",ret.getString("ownercode"));
				//货主姓名
 				data.put("custName",ret.getString("ownername"));
				//货主税号
 				data.put("custTaxNum",ret.getString("OwnerTariff"));
				//仓储状态
 				data.put("status",ret.getString("status"));

				//交易品种名称
 				data.put("prdCataName",ret.getString("GoodsProductName"));
				//交易品种代码
 				data.put("prdCataCode",ret.getString("warecode"));
				//海关商品编号
 				data.put("ctmPrdCode",ret.getString("warecstmno"));

				//品名描述
 				data.put("typeName",ret.getString("GoodsProductName"));
				//规格描述
 				data.put("specDesc",ret.getString("GoodsRule"));
				//捆包号
 				data.put("packNum",ret.getString("SheetNum"));
				//资源号
 				data.put("resourceNum",ret.getString("ResourceCode"));

				//库区描述
 				data.put("area",ret.getString("placezone"));
				//库位描述
 				data.put("location",ret.getString("Location"));
				//产地
 				data.put("areaP",ret.getString("Place"));
				//材质
 				data.put("grade",ret.getString("Texture"));

				//重量
 				data.put("weightB",ret.getString("WeightB"));
				//重量单位
 				data.put("weightUnit",ret.getString("WeightUnit"));

				//数量
 				data.put("sheetNum",ret.getString("SheetNum"));
				//数量单位
 				data.put("unit",ret.getString("SheetNumUnit"));

				//品质描述
 				data.put("qualityDesc",ret.getString("QualityDesc"));
				//外观描述
 				data.put("showDesc",ret.getString("ShowDesc"));

				//产品等级
 				data.put("productGrade",ret.getString("GoodsLevel"));
				//入库日期
 				data.put("storeDate",ret.getString("StorageTime"));
				//计量方式
 				data.put("checkType",ret.getString("CheckType"));

				//包装方式
 				data.put("packTypeName",ret.getString("PackType"));
				//危险等级
 				data.put("dangerousLevel",ret.getString("DangerLevel"));

 				
            }else{

            	return null;
            }

            ret.close();  
            db1.close();//关闭连接  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } 

        return  data;
	}


}	